import re
import time
import requests
import uuid
import datetime
from flask import make_response, request, send_from_directory
from flask_restful import abort
from flask_login import current_user, login_user
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial
import json
import logging
from flask_mail import Message

from redash import models, limiter, mail, settings
from redash.permissions import (
    require_default,
    require_admin,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings
from redash.serializers import (
    serialize_data_result_to_xlsx,
)
import sys
import random
from typing import List


class ProjectDetailNewResource(BaseResource):
    def get(self):
        result = models.ProjectDetail.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        projectdetail_id=req["id"]
        socialsecurity = req["socialsecurity"] if len(str(req["socialsecurity"]))>0 else 0
        periods = req["periods"] if len(str(req["periods"]))>0 else 0
        code = req["code"]
        schooled =  req["schooled"] if "schooled" in req else ""
        projectid = req["projectid"]
        projectcheck = models.ProjectDetail.get_repeat_employee(projectid)
        if projectcheck[0]<1:
            abort(400, message="添加的人员不能大于需求数量.")

        projectdetail = models.ProjectDetail(
        id=projectdetail_id,
        projectid = projectid,
        bill_date = req["bill_date"],
        employeeid = req["employeeid"],
        code = code,
        name = req["name"],
        amount = req["amount"],
        signamount = req["signamount"],
        major =req["major"],
        schooled =schooled,
        certificate =req["certificate"],
        location =req["location"],
        socialsecurity =socialsecurity,
        area = req["area"],
        periodunit = req["periodunit"],
        periods = periods,
        signingtime = req["signingtime"],
        expirationtime = req["expirationtime"],
        message = req["message"],       
        userid = req["userid"],
        contractcode= req["contractcode"],
        status = req["status"],
        orgunitid = req["orgunitid"] if "orgunitid" in req else "0",
        )
        try:
            models.db.session.add(projectdetail)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class ProjectDetailResource(BaseResource):
    def get(self, projectdetail_id):
        result = models.ProjectDetail.get_by_id(projectdetail_id)
        return result.to_dict()

    @require_default
    def post(self, projectdetail_id):
        req = request.get_json(force=True)
        require_fields(req, ("code","name"))
        socialsecurity = req["socialsecurity"] if len(str(req["socialsecurity"]))>0 else 0
        periods = req["periods"] if len(str(req["periods"]))>0 else 0
        code = req["code"]
        schooled =  req["schooled"] if "schooled" in req else ""

        projectdetail = models.ProjectDetail.get_by_id(projectdetail_id)

        params = dict(projectid = req["projectid"], employeeid = req["employeeid"], code = code, name = req["name"],
        amount = req["amount"], major =req["major"], schooled =schooled, certificate =req["certificate"],
        signamount = req["signamount"],
        location =req["location"], socialsecurity =socialsecurity, area = req["area"], periodunit = req["periodunit"],
        periods = periods, signingtime = req["signingtime"], expirationtime = req["expirationtime"],
        status = req["status"],orgunitid = req["orgunitid"] if "orgunitid" in req else "0",
        message = req["message"], contractcode= req["contractcode"])
        try:
            self.update_model(projectdetail, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)

class ProjectDetaildeleteResource(BaseResource):
    @require_default
    def post(self, projectdetail_id):
        projectdetail =  models.ProjectDetail.get_by_id(projectdetail_id)
        models.db.session.delete(projectdetail)
        models.db.session.commit()
    
class ProjectDetailListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        projectid =  str(req["projectid"]) if "projectid" in req else ""
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        certificate = req["certificate"] if "certificate" in req else ""
        location = req["location"] if "location" in req else ""

        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        else:
            orderfield ="t1.code"        

        strwhere ="1=1"
        if code !="" :
            strwhere = strwhere + " and t1.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"
        if major !='' :
            strwhere = strwhere + " and  '"+major+"' = ANY(t1.major)"
        if projectid !='' :
            strwhere = strwhere + " and t1.projectid = '"+projectid+"'"
            orderfield ="t1.id"        
        if certificate !='' :
            strwhere = strwhere + " and '"+certificate+"' = ANY(t1.certificate)"
        if location !='' :
            strwhere = strwhere + " and t1.location like '%"+location+"%'"

        strgetfield =""" t1.*,COALESCE(t2.name,'') as username """

        tblname = """ public.projectdetail t1 left join public.users t2 on t1.userid=t2.id  """
        #orderfield ="t1.id"
        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class ProjectDetailBatchAddResource(BaseResource):
    @require_default
    def post(self):
        req = request.get_json(force=True)
        userid = str(req["userid"]) if "userid" in req else "0"
        projectid = str(req["projectid"]) if "projectid" in req else ""
        employeeids = req["employeeids"]
        amount= req["amount"]
        neednum= req["neednum"]
        projectcheck = models.ProjectDetail.get_repeat_employee(projectid)
        if projectcheck[0]<len(employeeids):
            abort(400, message="添加的人员不能大于需求数量.")

        employee=",".join('%s' %id for id in employeeids)


        strsql ="""insert into public.projectdetail(projectid,employeeid,code,name,amount,signamount,major,schooled,certificate,
            location,socialsecurity,area,periodunit,periods,signingtime,expirationtime,
            message,contractcode,userid,bill_date,orgunitid,status)
            select  '"""+projectid+"""',id,code,name,round("""+str(amount)+""",2),signamount,major,schooled,certificate,
            location,socialsecurity,area,periodunit,periods,signingtime,expirationtime,
            message,contractcode,"""+userid+""",now(),'"""+str(self.current_user.orgunit_id)+"""',0
            From public.employee  where id in ( """+employee+""")"""
        try:
            models.db.session.execute(strsql)
            models.db.session.commit()
            return {"code": "sucess","message":"操作成功。"}
        except :
            return {"code": "err","message":"操作失败。"}

#     选单
class ProjectDetailSelListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        bytstatus = req["bytstatus"]
        paytype = str(req["paytype"]) if "paytype" in req else "0"
        billid = str(req["billid"])  if "billid" in req else "0"

        strsql ="""select t2.id,t2.projectid,t2.employeeid,t2.code,t2.name,
            t2.amount- COALESCE(t4.amount,0) as amount,
            t2.major,t2.schooled,t2.certificate,t2.location,t2.socialsecurity,
            t2.area,t2.periodunit,t2.periods,t1.signingtime,t1.expirationtime,
            t2.message,t2.contractcode,t3.userid,t3.maintainer,t3.signatory,
            t3.accountname,t3.accountcode,t3.accountbank,
            t2.signamount- COALESCE(t4.amount,0) as signamount,
            t1.customerid
            From public.project t1 inner join public.projectdetail t2 on t1.id=t2.projectid
            inner join public.employee t3 on t2.employeeid=t3.id
            left join (select t2.projectid,t2.projectdetailid, sum(t2.amount) as amount
            from public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            where t1.type="""+paytype+"""
            group by t2.projectid,t2.projectdetailid) t4 on t1.id=t4.projectid and t2.id=t4.projectdetailid
            where t1.status=2 and t1.org_id=""" +str(self.current_org.id) +" and (( "
        # if bytstatus !='' :
        #     strsql = strsql+ """ and t1.bytstatus='"""+bytstatus+"""' """
        if paytype=="0":
            strsql = strsql+ """ t2.signamount- COALESCE(t4.amount,0)>0"""
        else:
            strsql = strsql+ """ t2.amount- COALESCE(t4.amount,0)>0"""
        strsql = strsql+ """) or t2.id in(select projectdetailid from public.paymentactivity where paymentid="""+billid+"""))"""

        strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
        result = models.Enums.get_selsql(strsql)
        return result[0]
    
class ProjectDetailEmployeeListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        customername = req["customername"]if "customername" in req else ""
        representative = req["representative"]if "representative" in req else ""
        orgunitname = req["orgunitname"]if "orgunitname" in req else ""
        difference = str(req["difference"]) if "difference" in req else ""
        code = req["code"] if "code" in req else ""
        name = req["name"] if "name" in req else ""
        major = req["major"] if "major" in req else ""
        area = req["area"] if "area" in req else ""
        userid = str(req["userid"]) if "userid" in req else ""
        bytstatus= req["bytstatus"] if "bytstatus" in req else ""
        certificate = req["certificate"] if "certificate" in req else ""
        location = req["location"] if "location" in req else ""
        status = str(req["status"]) if "status" in req else ""
        begindate= req["begindate"] if "begindate" in req else ""
        enddate= req["enddate"] if "enddate" in req else ""
        genjinbegindate= req["genjinbegindate"] if "genjinbegindate" in req else ""
        genjinenddate= req["genjinenddate"] if "genjinenddate" in req else ""
        datadown = req["datadown"] if "datadown" in req else False
        orderfield = req["orderfield"] if "orderfield" in req else "code"
        order = req["order"] if "order" in req else 0
        if orderfield=="code" :
            orderfield ="t1.code"
        elif orderfield=="name":
            orderfield ="t1.name"
        elif orderfield=="major":
            orderfield ="t1.major"
        elif orderfield=="area":
            orderfield ="t1.area"
        elif orderfield=="bill_dateformat":
            orderfield ="t1.bill_date"
        elif orderfield=="genjindate":
            orderfield ="t10.genjindate"
        else:
            orderfield ="t1.code"        

        strwhere ="t1.org_id=" +str(self.current_org.id)
        if code !="" :
            strwhere = strwhere + " and t1.code like '%"+code+"%'"
        if name !='' :
            strwhere = strwhere + " and t1.name like '%"+name+"%'"
        if major !='' :
            strwhere = strwhere + " and  '"+major+"' = ANY(t1.major)"
        if area !='' :
            strwhere = strwhere + " and t1.area like '%"+area+"%'"
        if bytstatus !='' :
            strwhere = strwhere + " and t1.bytstatus like '%"+bytstatus+"%'"
        if certificate !='' :
            strwhere = strwhere + " and '"+certificate+"' = ANY(t1.certificate)"
        if location !='' :
            strwhere = strwhere + " and t1.location like '%"+location+"%'"
        if customername !='' :
            strwhere = strwhere + " and t3.strcustomername like '%"+customername+"%'"
        if orgunitname !='' :
            strwhere = strwhere + " and t5.name like '%"+orgunitname+"%'"
        if status !='' :
            strwhere = strwhere + " and t1.status = '"+status+"'"
        if representative !='':
            strwhere = strwhere + " and t3.representative like '%"+representative+"%'"
        if begindate !='' :
            strwhere = strwhere + " and t1.bill_date >= '"+begindate+"'"
        if enddate !='' :
            strwhere = strwhere + " and to_char(t1.bill_date,'YYYY-MM-DD') <= '"+enddate+"'"
        if genjinbegindate !='' :
            strwhere = strwhere + " and t10.genjindate >= '"+genjinbegindate+"'"
        if genjinenddate !='' :
            strwhere = strwhere + " and to_char(t10.genjindate,'YYYY-MM-DD') <= '"+genjinenddate+"'"
        if userid !="":
            strwhere = strwhere + " and t8.userid = '"+userid+"'"
            
        if "super_admin" not in self.current_user.permissions  and userid !="":
            strwhere = strwhere + """ and  (t8.userid='""" + userid + """' or  t8.userid in (
                WITH RECURSIVE cte(keys,items,parentkeys) AS 
                (
                    select *From (with RECURSIVE le (keys,items,parentkeys) as
                    (select   (items)->>'key' as keys,json_array_elements((items)->'children') as items,'0' as parentkeys
                    from   (select unnest(details) items From orgstruct where orgstruct_enum='admin' and expire_date is null) AA
                    union all
                    select (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys from le)
                    select le.* from le inner join users on to_number(le.keys, '9999999999999999999')= users.orgunit_id where users.id='""" + userid + """') T1
                    UNION ALL  
                    SELECT  (items)->>'key' as keys,json_array_elements((items)->'children') as items,keys as parentkeys FROM cte
                )
                SELECT users.id FROM cte inner join users on to_number((cte.items)->>'key', '9999999999999999999')=users.orgunit_id
                ))"""
        strgetfield =""" t8.projectid,t8.id,to_char(t1.bill_date,'YYYY-MM-DD') bill_dateformat,t1.code,t1.name,t1.customerid,t1.neednum,t1.amount,"""
        strgetfield =strgetfield +""" array_to_string(t1.major,',') major,t1.schooled,array_to_string(t1.certificate,',') certificate,t1.location,
                case when t1.socialsecurity=1 then '有' else '无' end socialsecuritystr,
                t1.area,t1.periodunit,t1.periods,t1.bytstatus,t1.signingtime,t1.expirationtime,t8.status,
                t1.purpose,t1.certificatestatus,t1.locationneed,t1.message,t1.signatory,t8.userid,t1.updated_at,t1.created_at,
                t8.orgunitid,t8.contractcode,t1.threeperson,case when t8.status=0 then '草稿' when t8.status=1 then '审核中' else '已审核' end as strstatus,
                COALESCE(t2.name,'') as username,COALESCE(t3.strcustomername,'') as strcustomername,COALESCE(t4.name,'')  as signatoryname,
                COALESCE(t3.representative,'') as representative,
                COALESCE(t5.name,'') as orgunitname,COALESCE(t7.employeecount,0) employeecount,
                t1.neednum-COALESCE(t7.employeecount,0) as difference,
                t8.code as employeecode,t8.name as employeename,t8.amount as employeeamount,t8.signamount as employeesignamount,
                array_to_string(t8.major,',') as employeemajor,t8.schooled as employeeschooled,
                array_to_string(t8.certificate,',') as employeecertificate,t8.location as employeelocation,
                COALESCE(t9.amount,0) as amountin,COALESCE(to_char(t9.paydate,'YYYY-MM-DD'),'') paydateformatin,
                COALESCE(to_char(t10.genjindate,'YYYY-MM-DD'),'') genjindate """

        tblname = """ public.project t1  left join public.customers t3 on t1.customerid=t3.id
                left join public.users t4 on t1.signatory=t4.id
                left join (select count(*) as employeecount,projectid From public.projectdetail group by projectid) t7 on t1.id=t7.projectid
                inner join  public.projectdetail t8 on t1.id=t8.projectid left join public.users t2 on t8.userid=t2.id
                left join public.orgunit t5 on t8.orgunitid=t5.id
                left join (select sum(t2.amount) as amount,t2.projectdetailid,t2.contractcode,
                max(t1.paydate) paydate
                From public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
                where t1.type=1 group by t2.projectdetailid,t2.contractcode) t9 on t8.id=t9.projectdetailid and t8.contractcode=t9.contractcode
                left join(select employeeid,max(updated_at) as genjindate From public.employeeactivity 
				where sourcetype='project'group by employeeid) t10 on t1.id=t10.employeeid """
        #orderfield ="t1.id"
        if not datadown :
            result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
            return result[0]
        else :
            showfieldparam="projecttoolbar1"
            if difference =='2' :
                showfieldparam="projecttoolbar2"
            queryshowfield = models.QueryShowField.get_by_code(showfieldparam)
            ps = queryshowfield.to_dict()
            fieldsshow = ps['fieldsshow']
            fieldsall = json.loads(json.dumps(ps['fieldsall'])) 
            columns =[]
            strsql="select " + strgetfield + " from " + tblname +" where " +strwhere
            strgetfield = ""
            for i in range(len(fieldsall)):
                if fieldsall[i]["key"] in fieldsshow:
                    columns.append({ 'name' : fieldsall[i]["title"]})
                    strgetfield = strgetfield +","+  fieldsall[i]["key"] +" as "+ fieldsall[i]["title"]
                    
            orderfield = req["orderfield"] if "orderfield" in req else "code"
            strsql = "select " + strgetfield[1:] + " from (" + strsql + ") t1 order by "+orderfield
            if order==0 :
                strsql= strsql+" ASC "
            else :
                strsql= strsql+" DESC "
            strsql = """select array_to_json(array_agg(row_to_json(t))) from (""" + strsql + """) t """
            data = models.Enums.get_selsql(strsql)

            article_info = {}
            da1 = json.loads(json.dumps(article_info))
            da1['columns'] = columns #[{"name": "code", "friendly_name": "code", "type": null}, {"name": "name", "friendly_name": "name", "type": null}]
            da1['rows'] = data[0] #[{"code": "code", "name": "name"}, {"code": "code1", "name": "name1"}]

            query_result = da1

            result = serialize_data_result_to_xlsx(query_result,"xiangmurenyuan")
            return send_from_directory(result, "xiangmurenyuan.xlsx",as_attachment=True)